package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

import co.gov.medellin.ssti.sub.common.excepcion.SUBException;
import co.gov.medellin.ssti.sub.model.dao.IRenovarDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;
import co.gov.medellin.ssti.sub.model.vo.AtributoVO;
import co.gov.medellin.ssti.sub.model.vo.BeneficiarioVO;

public class RenovarDAOImpl extends BaseDAOImpl implements IRenovarDAO {
	private static final String SELECT_SUBP_ATR_BENEFICIARIO_BY_SBPR_CODIGO = "SELECT CODIGO, NOMBRE, TIPO_DATO, TIPO_ATRIBUTO "
			+ "FROM TCS_BEN_SUBP_ATR_BENEFICIARIO WHERE SBPR_CODIGO = ? AND VIGENCIA='A' AND CAMPO_A2 IS NULL";

	private static final String SELECT_BENEFICIARIO_ATRIBUTO_VALOR_NUM_ATR = "SELECT VALOR_NUM_ATR "
			+ " valor FROM TCS_BEN_BENEFICIARIO_ATRIBUTO "
			+ "WHERE SAB_CODIGO = ? AND BEN_CODIGO = ? ";
	private static final String SELECT_BENEFICIARIO_ATRIBUTO_VALOR_FEC_ATR = "SELECT TO_CHAR(VALOR_FEC_ATR,'DD/MM/YYYY') "
			+ " valor FROM TCS_BEN_BENEFICIARIO_ATRIBUTO "
			+ " WHERE SAB_CODIGO = ? AND BEN_CODIGO = ? ";
	private static final String SELECT_BENEFICIARIO_ATRIBUTO_VALOR_CAR_ATR = "SELECT VALOR_CAR_ATR "
			+ " valor FROM TCS_BEN_BENEFICIARIO_ATRIBUTO "
			+ " WHERE SAB_CODIGO = ? AND BEN_CODIGO = ? ";
	private static final String SELECT_BENEFICIO_ATRIBUTO_VALOR_NUM_ATR = "SELECT VALOR_NUM_ATR "
			+ " valor FROM TCS_BEN_BENEFICIO_ATRIBUTO WHERE BOT_CODIGO = ? "
			+ " AND SAB_CODIGO = ? ";
	private static final String SELECT_BENEFICIO_ATRIBUTO_VALOR_FEC_ATR = "SELECT TO_CHAR(VALOR_FEC_ATR,'DD/MM/RRRR') "
			+ " valor FROM TCS_BEN_BENEFICIO_ATRIBUTO WHERE BOT_CODIGO = ? "
			+ " AND SAB_CODIGO = ? ";
	private static final String SELECT_BENEFICIO_ATRIBUTO_VALOR_CAR_ATR = "SELECT VALOR_CAR_ATR "
			+ " valor FROM TCS_BEN_BENEFICIO_ATRIBUTO WHERE BOT_CODIGO = ? "
			+ " AND SAB_CODIGO = ? ";

	private static final String SELECT_MAX_COD_BENEFICIO_OTORGADO_BY_SUBP_COD_BEN_COD = "SELECT MAX(CODIGO) codbot, SEDE_CODIGO sede FROM TCS_BEN_BENEFICIO_OTORGADO "
			+ " WHERE SBPR_CODIGO = ? AND BEN_CODIGO = ? "
			+ " AND FECHA_BENEFICIO=(SELECT MAX(FECHA_BENEFICIO) FROM TCS_BEN_BENEFICIO_OTORGADO "
			+ " WHERE SBPR_CODIGO = ? AND BEN_CODIGO = ? ) "
			+ " GROUP BY SEDE_CODIGO";

	private static final String SELECT_COD_BENEFICIO_OTORGADO_BY_CRITERIA = "SELECT CODIGO FROM TCS_BEN_BENEFICIO_OTORGADO "
			+ " WHERE SBPR_CODIGO = ? AND BEN_CODIGO = ? "
			+ " AND SEDE_CODIGO = ? AND CAMPO_A1 IS NULL";

	public RenovarDAOImpl(Connection con) {
		super(con);
	}

	public Collection<AtributoVO> findAtributosSubpro(Long codeSubprograma)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		List<AtributoVO> lAtributos = new LinkedList<AtributoVO>();
		try {
			pstmt = conn
					.prepareStatement(SELECT_SUBP_ATR_BENEFICIARIO_BY_SBPR_CODIGO);
			pstmt.setLong(1, codeSubprograma.longValue());
			rst = pstmt.executeQuery();
			AtributoVO objatri;
			while (rst.next()) {
				objatri = new AtributoVO();
				objatri.setCodatributo(rst.getString("CODIGO"));
				objatri.setNombre(rst.getString("NOMBRE"));
				objatri.setTipodato(rst.getString("TIPO_DATO"));
				objatri.setTipoatributo(rst.getString("TIPO_ATRIBUTO"));
				lAtributos.add(objatri);
				objatri = null;
			}
		} catch (SQLException e) {
			SUBException.logDebugError("Error findAtributosSubpro: ", e);
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lAtributos;
	}

	// este metodo parece duplicado en otro dao.
	public String findBeneficiarioAtri(String tipoDato, Long codeBene,
			Long codeAtri) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String consulta = "";
		String strValor = null;
		if (tipoDato.equals("N")) {
			consulta = SELECT_BENEFICIARIO_ATRIBUTO_VALOR_NUM_ATR;
		} else if (tipoDato.equals("F")) {
			consulta = SELECT_BENEFICIARIO_ATRIBUTO_VALOR_FEC_ATR;
		} else {
			consulta = SELECT_BENEFICIARIO_ATRIBUTO_VALOR_CAR_ATR;
		}

		try {
			pstmt = conn.prepareStatement(consulta);
			pstmt.setLong(1, codeAtri.longValue());
			pstmt.setLong(2, codeBene.longValue());
			rst = pstmt.executeQuery();

			if (rst.next()) {
				strValor = rst.getString("valor");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return strValor;
	}

	// TODO: que no retorne un ResultSet
	public ResultSet findBeneficioAtri(String tipoDato, Long codeBeneOtor,
			Long codeAtri) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String consulta = "";
		if (tipoDato.equals("N")) {
			consulta = SELECT_BENEFICIO_ATRIBUTO_VALOR_NUM_ATR;
		} else if (tipoDato.equals("F")) {
			consulta = SELECT_BENEFICIO_ATRIBUTO_VALOR_FEC_ATR;
		} else {
			consulta = SELECT_BENEFICIO_ATRIBUTO_VALOR_CAR_ATR;
		}

		try {
			pstmt = conn.prepareStatement(consulta);
			pstmt.setLong(1, codeBeneOtor.longValue());
			pstmt.setLong(2, codeAtri.longValue());
			rst = pstmt.executeQuery();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return rst;
	}

	public List<String> findBeneficioOtorgado(Long codeSubpro, Long codeBene)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<String> lretornar = new LinkedList<String>();
		try {
			pstmt = conn
					.prepareStatement(SELECT_MAX_COD_BENEFICIO_OTORGADO_BY_SUBP_COD_BEN_COD);
			pstmt.setLong(1, codeSubpro.longValue());
			pstmt.setLong(2, codeBene.longValue());
			pstmt.setLong(3, codeSubpro.longValue());
			pstmt.setLong(4, codeBene.longValue());
			rst = pstmt.executeQuery();

			if (rst.next()) {
				lretornar.add(rst.getString("codbot"));
				lretornar.add(rst.getString("sede"));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lretornar;
	}

	public void findBeneOtorDeshabilitar(Long codeSubpro, Long codeBene,
			Long codeSede) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		try {
			pstmt = conn
					.prepareStatement(SELECT_COD_BENEFICIO_OTORGADO_BY_CRITERIA);
			pstmt.setLong(1, codeSubpro.longValue());
			pstmt.setLong(2, codeBene.longValue());
			pstmt.setLong(3, codeSede.longValue());
			rst = pstmt.executeQuery();
			while (rst.next()) {
				int codBeneotor = rst.getInt("CODIGO");
				updateBeneotorCampoA1(codBeneotor);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
	}

	public List<BeneficiarioVO> findBeneficiarios(Long codeSubpro, Long codeSede)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String SEL = " SELECT DISTINCT bot.BEN_CODIGO codebene,ben.PRIMER_NOMBRE nom1bene,ben.SEGUNDO_NOMBRE nom2bene, "
				+ " ben.PRIMER_APELLIDO ape1bene, ben.SEGUNDO_APELLIDO ape2bene, ben.TIPO_DOCUMENTO tipodoc, "
				+ " ben.NUMERO_DOCUMENTO numdoc, TO_CHAR "
				+ " ((SELECT MAX(FECHA_BENEFICIO) "
				+ " FROM TCS_BEN_BENEFICIO_OTORGADO bot1,TCS_BEN_BENEFICIARIO ben1 "
				+ " WHERE bot1.SBPR_CODIGO = ? AND bot1.SEDE_CODIGO = ? "
				+ " AND bot1.BEN_CODIGO=ben1.codigo),'DD/MM/RRRR')fecha "
				+ " FROM TCS_BEN_BENEFICIO_OTORGADO bot,TCS_BEN_BENEFICIARIO ben, "
				+ " TCS_BEN_SUBPROGRAMA sub "
				+ " WHERE bot.SBPR_CODIGO = ? AND  bot.SEDE_CODIGO = ? "
				+ " AND ben.CODIGO = bot.BEN_CODIGO "
				+ " AND sub.CODIGO = bot.SBPR_CODIGO "
				+ " AND (CASE WHEN DECODE(bot.CAMPO_A1,'I',-1,TO_NUMBER(SYSDATE - (SELECT MAX(CAMPO_F1) "
				+ "                                                               FROM TCS_BEN_BENEFICIO_OTORGADO "
				+ "                                                               WHERE SBPR_CODIGO = ? "
				+ "                                                               AND BEN_CODIGO=ben.CODIGO "
				+ "                                                               AND SEDE_CODIGO = ? "
				+ " ))) "
				+ " >= to_number(nvl(sub.CAMPO_A3,0)+1) "
				+ " THEN 'Si' ELSE 'No' END) = 'Si' ";

		List<BeneficiarioVO> lbeneficiarios = new LinkedList<BeneficiarioVO>();
		BeneficiarioVO benevo = null;
		try {
			pstmt = conn.prepareStatement(SEL);
			pstmt.setLong(1, codeSubpro.longValue());
			pstmt.setLong(2, codeSede.longValue());
			pstmt.setLong(3, codeSubpro.longValue());
			pstmt.setLong(4, codeSede.longValue());
			pstmt.setLong(5, codeSubpro.longValue());
			pstmt.setLong(6, codeSede.longValue());
			rst = pstmt.executeQuery();

			while (rst.next()) {
				benevo = new BeneficiarioVO();
				benevo.setCode(new Long(rst.getString("codebene")));
				benevo.setNombre1(rst.getString("nom1bene"));
				benevo.setNombre2(rst.getString("nom2bene"));
				benevo.setPrimerApellido(rst.getString("ape1bene"));
				benevo.setSegundoApellido(rst.getString("ape2bene"));
				if (rst.getString("tipodoc").equals("O")) {
					benevo.setTipoDocumento("NO TIENE");
				} else if (rst.getString("tipodoc").equals("C")) {
					benevo.setTipoDocumento("CEDULA DE CIUDADANIA");
				} else if (rst.getString("tipodoc").equals("E")) {
					benevo.setTipoDocumento("CEDULA DE EXTRANJERIA");
				} else if (rst.getString("tipodoc").equals("N")) {
					benevo.setTipoDocumento("NIT");
				} else if (rst.getString("tipodoc").equals("P")) {
					benevo.setTipoDocumento("PASAPORTR");
				} else if (rst.getString("tipodoc").equals("R")) {
					benevo.setTipoDocumento("REGISTRO CIVIL");
				} else if (rst.getString("tipodoc").equals("T")) {
					benevo.setTipoDocumento("TARJETA DE IDENTIDAD");
				} else if (rst.getString("tipodoc").equals("I")) {
					benevo.setTipoDocumento("NIP");
				} else if (rst.getString("tipodoc").equals("U")) {
					benevo.setTipoDocumento("NUIP");
				} else if (rst.getString("tipodoc").equals("S")) {
					benevo.setTipoDocumento("NISE");
				} else if (rst.getString("tipodoc").equals("A")) {
					benevo.setTipoDocumento("CABILDO");
				}

				benevo.setNumeroDocumento(rst.getString("numdoc"));
				benevo.setFechaNacimiento(rst.getString("fecha"));
				lbeneficiarios.add(benevo);
				benevo = null;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lbeneficiarios;
	}

	private void updateBeneotorCampoA1(int codeBeneotor) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "UPDATE TCS_BEN_BENEFICIO_OTORGADO SET "
				+ "CAMPO_A1 = 'I' WHERE CODIGO = ? ";

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, codeBeneotor);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			SUBException.logDebugError("", e);
		} finally {
			close(rst, pstmt);
		}
	}

}
